using System;
using System.Data;
using System.Data.SqlClient;

/*
	Jun 2012: Added the nullable field info pointer 
*/
namespace SqlServerDriver
{
	/// <summary>
	/// Summary description for Helper.
	/// </summary>
	internal class Helper
	{
		public Helper() { }

		public static String MakeSearchClause(String seek)
		{
			String clause = String.Format("like \'%{0}%\'", seek.Trim());
			if (seek.Length == 0)
				return clause;
			// get beginning and end chars
			char first = seek[0];
			char last = seek[seek.Length - 1];
			// use regex 
			if (first == '^' && last == '$')
				return String.Format("= '{0}'", seek.Substring(1, seek.Length - 2));
			// assume it will be "like '%xxx%'
			if (first == '^')
				return String.Format("like \'{0}%\'", seek.Substring(1, seek.Length - 1).Trim());
			else
				// assume it will be "like '%xxx%'
				if (last == '$')
					return String.Format("like \'%{0}\'", seek.Substring(0, seek.Length - 1));
			return clause;
		}

		public static String SafeSqlField(String field)
		{
			if (field.Length == 0)
				return field;
			if (field[0] == '[' && field[field.Length - 1] == ']')
				return field;
			// hacked fix for aliased fields.
			if (field.IndexOf('.') > 0)
				return field;
			return "[" + field.Trim() + "]"; // does not allow for fields with leading & trailing spaces
		}

		public static String SafeTableName(String tableName)
		{
			// empty string: reflect it back
			if (tableName.Length == 0) return tableName;
			// already escaped apparently: nothing to do
			if (tableName[0] == '[' && tableName[tableName.Length - 1] == ']')
				return tableName;
			// 
			// Experimental: if a sub query then skip
			//
			if (tableName.Replace(" ", "").Replace("\n", "").Replace("\r", "").ToUpper().IndexOf("(SELECT") != -1) return tableName;
			// no schema: just [ tablename ]
			if (tableName.IndexOf('.') == -1)
				return "[" + tableName + "]";
			// Schema.tablename -> [Schema].[tablename]
			return "[" + tableName.Replace(".", "].[") + "]";
		}

		public static string SafeData(String dataValue)
		{
			return dataValue.Replace("'", "''");
		}

		internal static bool TableIsSubQuery(string tableName)
		{
			// to test whether the string is a true table (99.999% of the time)
			// or a subquery in a custom view
			// SafeTableName() adds leading and trailing bracket to a real table startin with (
			// remove any leading whitespace and check for (
			if (SafeTableName(tableName).Replace(" ", "").IndexOf('(') == 0)
				return true;
			return false;
		}

		public static void GenericMakeDset(String strConnectionString, String sql, String strTableName, out DataSet dset)
		{
			// OK this bit is not generic its safe to remove when porting the code to other projects
			ConnectionMap.Instance.Update(strConnectionString, DateTime.Now);

			// Create a data reading object to fill the dataset
			dset = new DataSet();
			SqlConnection sqlConn = new SqlConnection();
			try
			{
				// get the connection 
				sqlConn.ConnectionString = strConnectionString;
				sqlConn.Open();
				SqlDataAdapter da = new SqlDataAdapter(sql, sqlConn);
				
				// execute into something. Unconstrained version
				// da.Fill(dset, strTableName);
				
				// dbviewsharp limiter version. Magic number for now
				int rowLimit = 100000;  // seriously what would you do with over 100,000 rows in a datagrid.

				da.Fill(dset, 0, rowLimit + 1, strTableName); // if we get rowLimit + 1 then error 
				sqlConn.Close();
				// check the result if a dataset was returned
				if (dset.Tables.Count > 0)
				{
					if (dset.Tables[0].Rows.Count > rowLimit)
					{
						String message = String.Format("The result row count exceeded the DbViewSharp limit of {0}", rowLimit);
						throw new Exception(message);
					}
				}
			}
			catch (SqlException e)
			{
				// may not work if connection fails
				sqlConn.Close();
				String message = String.Format("Sql Error: {0}\n" +
											   "connection: {1}\n" +
												"Sql: {2}\n",
												e.Message, Depassword(strConnectionString), sql);
				Exception ex = new Exception(message);
				throw ex; // abort process having logged as much as possible.
			}
		}

		// Has knowledge of SqlServerDataSource.MakeConnectionString()
		private static String Depassword(string strConnectionString)
		{
			// knows this is the password tag and that it's the last item in the connection String
			int pos = strConnectionString.IndexOf(";password=");
			if (pos == -1) return strConnectionString; // should be Trusted connection in which case no password info is displayed.
			return strConnectionString.Substring(0, pos); // cut-off the password information
		}

		// simpler version. Only for a query returning a datatable
		public static DataTable GenericQuery(String strConnectionString, String sql)
		{
			DataSet dset;
			GenericMakeDset(strConnectionString, sql, "DATA", out dset);
			return dset.Tables[0];
		}

		public static String GenericScalarQuery(String strConnectionString, String sql, String defaultIfEmpty)
		{
			DataSet dset;
			GenericMakeDset(strConnectionString, sql, "DATA", out dset);
			DataTable dt = dset.Tables[0];
			// very loose interpretation of duff results.
			if (dt == null) return defaultIfEmpty;
			if (dt.Rows.Count == 0) return defaultIfEmpty;
			if (dt.Rows[0][0] == System.DBNull.Value) return defaultIfEmpty;
			return dt.Rows[0][0].ToString();
		}

		// Compatibility
		public static String GenericExecute(String strConnectionString, String sql)
		{
			bool succeeded;
			return GenericExecute(strConnectionString, sql, out succeeded);
		}

		public static String GenericExecute(String strConnectionString, String sql, out bool succeeded)
		{
			String returnMessage = "";
			succeeded = true; // assume it was OK
			// establish a SQL connection
			SqlConnection sqlConn = new SqlConnection();
			sqlConn.ConnectionString = strConnectionString;
			SqlCommand command = new SqlCommand(sql, sqlConn);
			try
			{
				command.Connection.Open();
				int rows = command.ExecuteNonQuery();
				returnMessage = "Command completed";
				if (rows != -1)
					returnMessage += String.Format("\n{0} rows affected", rows);
			}
			catch (System.Data.SqlClient.SqlException e)
			{
				returnMessage = e.Message;
				succeeded = false; // easier check on failure.
			}
			finally
			{
				command.Connection.Close();
			}
			return returnMessage;
		}
		public static bool TestConnection(String strConnectionString, out String message)
		{
			message = ""; // OK 
			bool isTestedOk = true;
			// Create a data reading object to fill the dataset
			DataSet dset = new DataSet();
			SqlConnection sqlConn = new SqlConnection();
			try
			{
				// get the connection 
				sqlConn.ConnectionString = strConnectionString;
				sqlConn.Open();

				SqlDataAdapter da = new SqlDataAdapter("SELECT @@VERSION", sqlConn);
				// execute into something
				da.Fill(dset, "VERSION");
				sqlConn.Close();
				message = "OK:\n" + dset.Tables[0].Rows[0][0].ToString();
			}
			catch (SqlException e)
			{
				message = String.Format("{0}", e.Message);
				// may not work if connection fails
				sqlConn.Close();
				isTestedOk = false;
			}
			return isTestedOk;
		}

		public static String DocFromDset(String strConnectionString, String sql, int sourceCol, bool addNewLine)
		{
			DataTable sourceLines = GenericQuery(strConnectionString, sql);

			// use stringbuilder? Nah! Only ever 1 or 2 rows 
			String sourceCode = "";
			foreach (DataRow r in sourceLines.Rows)
			{
				sourceCode += r[sourceCol].ToString();
				if (addNewLine)
					sourceCode += "\n";
			}
			return sourceCode;
		}

		public static String GetSourceCode(String strConnectionString, String strSelectedObject)
		{
			String searchTemplate = @"
SELECT so.name, sc.text 
  FROM sysobjects so 
 INNER JOIN syscomments sc ON so.id = sc.id 
-- WHERE so.id = object_id('{0}')  -- original: replace if any weirdness happens
 WHERE so.id = abs(object_id('{0}'))  -- abs() because of -ve system objects
ORDER BY sc.colid
";
			String sql = String.Format(searchTemplate, strSelectedObject);

			return DocFromDset(strConnectionString, sql, 1, false);
		}

		/*
			Breaks a table name into schema table
			but reverses the order so that the table name is always 1st.
		*/
		public static String[] ParseTableName(String tableName)
		{
			String[] result = tableName.Split('.');
			if (result.GetLength(0) > 1)
			{
				String tmp = result[1];
				result[1] = result[0];
				result[0] = tmp;
			}
			return result;
		}

		/*
				// obsolete
				public static void GetStructure(String strConnectionString, String strSelectedObject, out DataSet dset)
				{
					// if a view or table
					// build the query
					String strTmpl = "select top 0 * from [{0}]";
					String sql = String.Format(strTmpl, strSelectedObject);
					SqlConnection sqlConn = new SqlConnection();
					// get the connection 
					sqlConn.ConnectionString = strConnectionString;
					SqlCommand theCommand = new SqlCommand(sql, sqlConn);
					sqlConn.Open();
					SqlDataReader theReader;
					// Create a SQl reader
					dset = new DataSet();
					try
					{
						theReader = theCommand.ExecuteReader();
						// Create a data reading object to fill the dataset

						// have to add columns
						DataTable Src = theReader.GetSchemaTable();
						// remove some 
						Src.Columns.Remove("ColumnOrdinal");
						Src.Columns.Remove("NumericPrecision");
						Src.Columns.Remove("NumericScale");
						Src.Columns.Remove("IsUnique");
						Src.Columns.Remove("IsKey");
						Src.Columns.Remove("BaseCatalogName");
						Src.Columns.Remove("BaseColumnName");
						Src.Columns.Remove("BaseSchemaName");
						Src.Columns.Remove("BaseServerName");
						Src.Columns.Remove("BaseTableName");

						dset.Tables.Add(Src);
						sqlConn.Close();
					}
					catch (SqlException e)
					{
						// fake the message into a table
						DataTable dt = new DataTable("Error");
						dset.Tables.Add(dt);
						DataColumn ErrCol = dt.Columns.Add("Error", typeof(String));
						object[] DataArray = new object[1];
						DataRow dr = dt.NewRow();
						dt.Rows.Add(dr);
						DataArray[0] = e.Message;
						dr.ItemArray = DataArray;
						// may not work if connection fails
						sqlConn.Close();
					}
				}

				// obsolete
				public static void GetExtraTableStructureInfo(String strConnectionString, String strTableName, out DataSet dset)
				{
					// dig into extra info
					String strTmpl = "exec sp_help {0}";
					String sql = String.Format(strTmpl, strTableName);
					SqlConnection sqlConn = new SqlConnection();
					// get the connection 
					sqlConn.ConnectionString = strConnectionString;
					dset = new DataSet();
					try
					{
						sqlConn.Open();

						SqlDataAdapter da = new SqlDataAdapter(sql, sqlConn);
						// execute into something
						da.Fill(dset, strTableName);
						sqlConn.Close();
					}
					catch (SqlException e)
					{
						// fake the message into a table
						DataTable dt = new DataTable("Error");
						dset.Tables.Add(dt);
						DataColumn ErrCol = dt.Columns.Add("Error", typeof(String));
						object[] DataArray = new object[1];
						DataRow dr = dt.NewRow();
						dt.Rows.Add(dr);
						DataArray[0] = e.Message;
						dr.ItemArray = DataArray;
						// may not work if connection fails
						sqlConn.Close();
					}
				}
		*/
		//
		// new: from Sql datasource
		// 
		public static SqlServerTemplates.Version DetermineSqlVersion(String connStr)
		{
			String command = "SELECT SERVERPROPERTY('ProductVersion')";
			DataSet dataset = new DataSet();
			Helper.GenericMakeDset(connStr, command, "ServerProperties", out dataset);
			// Version
			String value = dataset.Tables[0].Rows[0][0].ToString();
			return SqlServerTemplates.DetermineSqlVersion(value);
		}

		//
		// adapted from SqlServerDataSource version. Requires additional server call for version so slower.
		//
		public static SqlTableSchema MakeSchemaFor(String tableName, String connStr)
		{
			// look up
			SqlServerTemplates.Version sqlVersion = DetermineSqlVersion(connStr);
			String commandTemplate = SqlServerTemplates.FetchTemplate(sqlVersion, DbView.Commands.Cmnd.Fields);
			// note: false is case-sensitive parameter, but that only changes code when the filter text is not
			// empty. In this case it is empty so this should work for case sensitive databases as well.
			commandTemplate = SqlServerTemplates.Templates.ProcessFilterClause(commandTemplate, "", false);
			String commandString = String.Format(commandTemplate, tableName);
			DataSet dataset = new DataSet();
			Helper.GenericMakeDset(connStr, commandString, tableName, out dataset);
			DataTable schemaTable = dataset.Tables[0];
			schemaTable.TableName = tableName;
			SqlTableSchema schema = new SqlTableSchema();
			schema.Populate(schemaTable, "Field Name", "Type", "Length", "Dp", "PKey?", "Auto?",
										 "FKey?", "Index?", "Calc?", "Def?", "Default/Computed", "nulls?");

			return schema;
		}

		// Imported from the application assembly. TODO move it to the library
		/* as you always forget
			dt.Rows.Add(new object[] { 1, "<clear filter>", "" });
		*/
		public static DataTable MakeDataTable(String name, String columnNames, String columnTypes)
		{
			String[] colNames = columnNames.Split('|');
			if (colNames.GetLength(0) != columnTypes.Length)
			{
				int n1 = colNames.GetLength(0);
				int n2 = columnTypes.Length;
				String msg = String.Format("Invalid table specification for MakeDataTable\n" +
										   "The number of columns must equal the number of column type characters.\n" +
										   "There are {0} names in {1}\n" +
										   "There are {2} chars in {3}", n1, columnNames, n2, columnTypes);
				throw new Exception(msg);
			}
			DataTable dt = new DataTable(name);
			for (int idx = 0; idx < colNames.GetLength(0); ++idx)
			{
				String colName = colNames[idx];
				String typeName = Helper.ResolveType(columnTypes[idx]);

				DataColumn col = new DataColumn();
				col.ColumnName = colName;
				col.DataType = System.Type.GetType(typeName);
				dt.Columns.Add(col);
			}
			return dt;
		}

		public static DataTable MakeSortedTable(DataTable source, String field, bool asc)
		{
			DataTable target = source.Clone(); // copy structure so rows can be received
			String sortClause = String.Format("{0} {1}", field, (!asc) ? "DESC" : "");
			DataRow[] sorted = source.Select("", sortClause);
			foreach (DataRow sortedRow in sorted)
				target.ImportRow(sortedRow);
			return target;
		}

		private static String ResolveType(char c)
		{
			switch (c)
			{
				case 'S': return "System.String";
				case 'I': return "System.Int32";
				case 'J': return "System.Int64";  // j = I++
				case 'N': return "System.Double";
				case 'D': return "System.DateTime";
				default:
					throw new Exception(String.Format("Invalid Type code {0} - expecting S,I,J, N,D", c));
			}
		}
		/* 
			Helper: remove a piece of SQL from a dynamic command. 
			The line is tagged with a comment
			eg.
			select * from TABLE
				where search='' -- Remove if no search
			the tag to search on is the comment "-- Remove if no search"
		*/
		public static string RemoveTaggedLine(string commandString, string tag)
		{
			int pos = commandString.IndexOf(tag);
			// tag not found. Do nothing.
			if (pos == -1) 
				return commandString;
			// find next line break;
			int breakPos = commandString.IndexOf('\n', pos); 
			string afterCode = (breakPos == -1) ? "" : commandString.Substring(breakPos + 1);
			// find first line break before the code.
			breakPos = commandString.LastIndexOf('\n', pos); 
			string beforeCode = (breakPos == -1) ? "" : commandString.Substring(0, breakPos - 1);
			return beforeCode + "\n" + afterCode;
		}

	}
}
